using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using MM.Entities;

namespace MM.Business
{
    public class clsPHIEU_XUAT_HANG
    {
        public clsPHIEU_XUAT_HANG(string sConn)
        {
            SqlHelper.ConnectionString = sConn;
        }

        #region Private methods
        private PHIEU_XUAT_HANG GetDataRow(DataRow row)
        {
            PHIEU_XUAT_HANG objPHIEU_XUAT_HANG = new PHIEU_XUAT_HANG();
            try
            {
                if (row["MaPhieuXuatHang"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.MaPhieuXuatHang = row["MaPhieuXuatHang"].ToString();
            }
            catch { }
            try
            {
                if (row["NgayXuat"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.NgayXuat = Convert.ToDateTime((row["NgayXuat"]));
            }
            catch { }
            try
            {
                if (row["NgayExport"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.NgayExport = Convert.ToDateTime((row["NgayExport"]));
            }
            catch { }
            try
            {
                if (row["NguoiXuat"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.NguoiXuat = (row["NguoiXuat"].ToString());
            }
            catch { }
            try
            {
                if (row["NguoiExport"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.NguoiExport = (row["NguoiExport"].ToString());
            }
            catch { }
            try
            {
                if (row["MaPXHThamChieu"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.MaPXHThamChieu = row["MaPXHThamChieu"].ToString();
            }
            catch { }
            try
            {
                if (row["TinhTrang"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.TinhTrang = int.Parse(row["TinhTrang"].ToString());
            }
            catch { }         
            try
            {
                if (row["SoLanExport"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.SoLanExport = int.Parse(row["SoLanExport"].ToString());
            }
            catch { }
            try
            {
                if (row["PlantReferenceSAP"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.PlantReferenceSAP = (row["PlantReferenceSAP"].ToString());
            }
            catch { }
            try
            {
                if (row["LoaiGI"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.LoaiGI = int.Parse(row["LoaiGI"].ToString());
            }
            catch { }  
             try
            {
                if (row["GhiChu"] != DBNull.Value)
                    objPHIEU_XUAT_HANG.GhiChu = (row["GhiChu"].ToString());
            }
            catch { }  

            return objPHIEU_XUAT_HANG;
        }
        private List<PHIEU_XUAT_HANG> GetPHIEU_XUAT_HANG(DataTable dt)
        {
            List<PHIEU_XUAT_HANG> lst = new List<PHIEU_XUAT_HANG>();
            PHIEU_XUAT_HANG obj;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj = this.GetDataRow(dt.Rows[i]);
                lst.Add(obj);
            }
            return lst;
        }
        #endregion Private Method

        #region Public methods
        public string InsertPHIEU_XUAT_HANG(PHIEU_XUAT_HANG obj, List<CHI_TIET_PXH> lstCT)
        {
            string spName = "";
            SqlParameter[] par;
            int iResult = 0;
            string maphieuxuathang = "";
            try
            {
                SqlHelper.BeginTran();
                spName = "usp_InsertPHIEU_XUAT_HANG";
                par = new SqlParameter[11];
                par[0] = new SqlParameter("@MaPhieuXuatHang", SqlDbType.VarChar, 10);
                par[0].Direction = ParameterDirection.Output;
                par[1] = new SqlParameter("@NgayXuat", obj.NgayXuat);
                par[2] = new SqlParameter("@NguoiXuat", obj.NguoiXuat);
                par[3] = new SqlParameter("@TinhTrang", obj.TinhTrang);
                par[4] = new SqlParameter("@SoLanExport", obj.SoLanExport);
                par[5] = new SqlParameter("@GIReferenceSAP", obj.GIReferenceSAP);
                par[6] = new SqlParameter("@MaPXHThamChieu", obj.MaPXHThamChieu);
                par[7] = new SqlParameter("@PlantReferenceSAP", obj.PlantReferenceSAP);
                par[8] = new SqlParameter("@NgayExport", null);
                par[9] = new SqlParameter("@NguoiExport", obj.NguoiExport);                
                par[10] = new SqlParameter("@MaTau", obj.MaTau);

                iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);

                maphieuxuathang = par[0].Value.ToString();
                if (maphieuxuathang.Length != 10)
                {
                    SqlHelper.Tran.Rollback();
                    iResult = -1;
                }
                else
                {
                    int i = 0;

                    for (i = 0; i < lstCT.Count; i++)
                    {
                        if (lstCT[i].TinhTrang == 0)
                        {
                            spName = "usp_InsertCHI_TIET_PXH";
                            par = new SqlParameter[12];
                            par[0] = new SqlParameter("@MaChiTiet", lstCT[i].MaChiTiet);
                            par[1] = new SqlParameter("@SoLuong", lstCT[i].SoLuong);
                            par[2] = new SqlParameter("@MaThietBi", lstCT[i].MaThietBi);
                            par[3] = new SqlParameter("@TinhTrang", lstCT[i].TinhTrang);
                            par[4] = new SqlParameter("@MaPhieuXuatHang", maphieuxuathang);
                            par[5] = new SqlParameter("@MaPhieuHuyXuatHang", lstCT[i].MaPhieuHuyXuatHang);
                            par[6] = new SqlParameter("@MaKho", lstCT[i].MaKho);
                            par[7] = new SqlParameter("@MaTrangThai", lstCT[i].MaTrangThai);
                            par[8] = new SqlParameter("@NguoiTao", obj.NguoiXuat);
                            par[9] = new SqlParameter("@NgayTao", obj.NgayXuat);
                            par[10] = new SqlParameter("@POThamChieuSAP", lstCT[i].POThamChieuSAP);
                            par[11] = new SqlParameter("@MaChiTietPDH", lstCT[i].MaChiTietPDH);


                            iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);

                            if (iResult <= 0)
                            {
                                SqlHelper.Tran.Rollback();
                                maphieuxuathang = "";
                                break;
                            }
                        }

                    }
                    if (i == lstCT.Count && i > 0)
                    {
                        SqlHelper.CommitTran();
                    }


                }



            }
            catch (Exception ex)
            {
                SqlHelper.Tran.Rollback();
                maphieuxuathang = "";

            }
            finally
            {
                SqlHelper.CloseConnection();
            }
            return maphieuxuathang;
        }

        public List<PHIEU_XUAT_HANG> usp_SelectPHIEU_XUAT_HANGsSearch(PHIEU_XUAT_HANG obj, List<VAT_TU_PHU_TUNG> lstVTPT,int DaDuyet)
        {
            string spName = "usp_SelectPHIEU_XUAT_HANGsSearch";
            DataTable dt = new DataTable();
            List<PHIEU_XUAT_HANG> lstpxh = new List<PHIEU_XUAT_HANG>();
            try
            {
                string temp = "";
                for (int i = 0; i < lstVTPT.Count; i++)
                {
                    if (i == 0)
                        temp = "(";
                    temp += "'" + lstVTPT[i]._maVTPT + "'";
                    if (i < lstVTPT.Count - 1)
                        temp += ",";
                    else
                        temp += ")";
                }
                SqlHelper.OpenConnection();
                SqlParameter[] par = new SqlParameter[8];
                par[0] = new SqlParameter("@MaPhieuXuatHang", obj.MaPhieuXuatHang == "" ? null : obj.MaPhieuXuatHang);
                if (obj.NgayXuatFrom.Year < 2012)
                    par[1] = new SqlParameter("@NgayTaoFrom", null);
                else
                    par[1] = new SqlParameter("@NgayTaoFrom", obj.NgayXuatFrom);
                if (obj.NgayXuatTo.Year < 2012)
                    par[2] = new SqlParameter("@NgayTaoTo", null);
                else
                    par[2] = new SqlParameter("@NgayTaoTo", obj.NgayXuatTo);
                par[3] = new SqlParameter("@NguoiTao", obj.NguoiXuat == "" ? null : obj.NguoiXuat);                
                par[4] = new SqlParameter("@MaKho", obj.MaKho == "" ? null : obj.MaKho);
                par[5] = new SqlParameter("@MaVTPTs", temp == "" ? null : temp);
                if (DaDuyet==0)// chua duyet
                    par[6] = new SqlParameter("@DaDuyet", false);
                else
                    if (DaDuyet==1)// dc duyet
                        par[6] = new SqlParameter("@DaDuyet", true);
                    else
                        par[6] = new SqlParameter("@DaDuyet", null);
                par[7] = new SqlParameter("@TinhTrang", obj.TinhTrang);

                dt = SqlHelper.ExecuteToDataTable(spName, par);
                lstpxh = this.GetPHIEU_XUAT_HANG(dt);

            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }


            return lstpxh;
        }

        public List<PHIEU_XUAT_HANG> usp_SelectPHIEU_XUAT_HANG(string MaPhieuXuatHang)
        {
            string spName = "usp_SelectPHIEU_XUAT_HANG";
            DataTable dt = new DataTable();
            List<PHIEU_XUAT_HANG> lst = new List<PHIEU_XUAT_HANG>();
            try
            {
                SqlHelper.OpenConnection();

                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@MaPhieuXuatHang", MaPhieuXuatHang);

                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, par);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, par);
                }

                lst = this.GetPHIEU_XUAT_HANG(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public List<PHIEU_XUAT_HANG> usp_SelectPHIEU_XUAT_HANG_MAX(string matau)
        {
            string spName = "select top 1 * from phieu_xuat_hang where maphieuxuathang like '%" + matau + "%' order by maphieuxuathang desc";
            DataTable dt = new DataTable();
            List<PHIEU_XUAT_HANG> lst = new List<PHIEU_XUAT_HANG>();
            try
            {
                SqlHelper.OpenConnection();
               
                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, CommandType.Text);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, CommandType.Text);
                }

                lst = this.GetPHIEU_XUAT_HANG(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public int usp_UpdatePHIEU_XUAT_HANG_Duyet(PHIEU_XUAT_HANG obj)
        {
            int iResult = 0;
            string spName = "usp_UpdatePHIEU_XUAT_HANG_Duyet";

            try
            {
                SqlParameter[] par = new SqlParameter[7];
                par[0] = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                par[0].Direction = ParameterDirection.ReturnValue;
                par[1] = new SqlParameter("@MaPhieuXuatHang", obj.MaPhieuXuatHang);
                par[2] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[3] = new SqlParameter("@NguoiExport", obj.NguoiExport);
                par[4] = new SqlParameter("@NgayTao", obj.NgayXuat);
                par[5] = new SqlParameter("@NguoiTao", obj.NguoiXuat);
                par[6] = new SqlParameter("@GhiChu", obj.GhiChu);



                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

                iResult = int.Parse(par[0].Value.ToString());

            }
            catch (Exception ex)
            {
            }
            return iResult;
        }

        public int usp_UpdatePHIEU_XUAT_HANG(PHIEU_XUAT_HANG obj)
        {
            int iResult = 0;
            string spName = "usp_UpdatePHIEU_XUAT_HANG";

            try
            {
                SqlParameter[] par = new SqlParameter[11];
                par[0] = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                par[0].Direction = ParameterDirection.ReturnValue;
                par[1] = new SqlParameter("@MaPhieuXuatHang", obj.MaPhieuXuatHang);
                par[2] = new SqlParameter("@NgayXuat", obj.NgayXuat);
                par[3] = new SqlParameter("@NguoiXuat", obj.NguoiXuat);
                par[4] = new SqlParameter("@TinhTrang", obj.TinhTrang);
                par[5] = new SqlParameter("@SoLanExport", obj.SoLanExport);
                par[6] = new SqlParameter("@GIReferenceSAP", obj.GIReferenceSAP);
                par[7] = new SqlParameter("@MaPXHThamChieu", obj.MaPXHThamChieu);
                par[8] = new SqlParameter("@PlantReferenceSAP", obj.PlantReferenceSAP);
                par[9] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[10] = new SqlParameter("@NguoiExport", obj.NguoiExport);
               

                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

                iResult = int.Parse(par[0].Value.ToString());

            }
            catch (Exception ex)
            {
            }
            return iResult;
        }

        public void CsvExportPHIEU_XUAT_HANG(PHIEU_XUAT_HANG pXH, List<CHI_TIET_PXH> lst, string filepath)
        {
            if (lst.Count == 0)
                return;
            StringBuilder sb = new StringBuilder();

            string d = "0" + pXH.NgayXuat.Day.ToString();
            d = d.Substring(d.Length - 2, 2);
            string m = "0" + pXH.NgayXuat.Month.ToString();
            m = m.Substring(m.Length - 2, 2);
            string y = pXH.NgayXuat.Year.ToString();

            sb.Append(pXH.MaPhieuXuatHang);
            sb.Append(";");
            sb.Append(pXH.POThamChieuSAP);
            sb.Append(";");
            sb.Append(d + "." + m + "." + y);
            sb.Append(";");
            sb.Append(pXH.GhiChu);
            if (pXH.TinhTrang == 0)
            {
                for (int i = 0; i < lst.Count; i++)
                {
                    if (lst[i].TinhTrang == 0)
                    {
                        sb.AppendLine();
                        sb.Append(lst[i].MaChiTietPDH);
                        sb.Append(";");
                        sb.Append(lst[i].MaThietBi);
                        sb.Append(";");
                        sb.Append(lst[i].SoLuong);
                        sb.Append(";");
                        sb.Append(lst[i].MaKhoThamChieuSAP);
                        sb.Append(";");
                        // GL ACC
                        sb.Append(";");
                        sb.Append(lst[i].MaChiTiet);
                        sb.Append(";");
                        sb.Append(lst[i].ValuationType);
                        
                    }
                }
            }

            File.WriteAllText(filepath, sb.ToString());
        }

        public void CsvExportPHIEU_XUAT_HANG_Cancelation(PHIEU_XUAT_HANG pXH, List<CHI_TIET_PXH> lst, string filepath)
        {
            if (lst.Count==0)
                return;
            StringBuilder sb = new StringBuilder();

            string d = "0" + lst[0].NgayHuyXuatHang.Value.Day.ToString();
            d = d.Substring(d.Length - 2, 2);
            string m = "0" + lst[0].NgayHuyXuatHang.Value.Month.ToString();
            m = m.Substring(m.Length - 2, 2);
            string y = lst[0].NgayHuyXuatHang.Value.Year.ToString();

            sb.Append(lst[0].MaPhieuHuyXuatHang);
            sb.Append(";");
            sb.Append(pXH.MaPhieuXuatHang);
            sb.Append(";");
            sb.Append(d + "." + m + "." + y);
            sb.Append(";");
            sb.Append(pXH.GhiChu);
            if (pXH.TinhTrang == 0)
            {
                for (int i = 0; i < lst.Count; i++)
                {
                    if (lst[i].TinhTrang == 0)
                    {
                        sb.AppendLine();
                        
                        sb.Append(lst[i].MaChiTiet);
                        sb.Append(";");
                        sb.Append(lst[i].MaThietBi);
                    }
                }
            }

            File.WriteAllText(filepath, sb.ToString());
        }
        #endregion Public methods
    }
}
